﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using Convert.LOTSmodel;
using Convert.CPIAMmodel;
using Convert.DataAccess;
using FuzzyMatching;
using System.Data.OleDb;
using LOTSConversion;
using System.Configuration;
using System.Windows;
namespace Convert.DBconverter
{
    class StockConverter
    {
            private static int BATCH_SAVE_SIZE = 5000;
            public static string LotsConnectionString; //= @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=D:\LOTS\lots.mdb";//@"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=D:\LOTS\lots.mdb";//ConfigurationManager.ConnectionStrings["LOTStoCPIAMconvert.Properties.Settings.lotsConnectionString"].ConnectionString;
            public static string CpiamConnectionString; //= ConfigurationManager.ConnectionStrings["LOTStoCPIAMconvert.Properties.Settings.CPIAMPOSMaxRetailConnectionString"].ConnectionString;
            public static string TempUpdatedStockTableName = "LOTS_UpdatedStockFiles";
            public static string TempReallocationsStockTableName = "LOTS_ReallocatedStockFiles";
            private static Dictionary<int, int> SupplierDictionary;
            private static Dictionary<string, int> LotsReallocations;
            private static Dictionary<Guid, int> CPIAMReallocations;
            private static Dictionary<int, Tuple<Guid, int>> LotsItemIDToCPIAMIDMap;
            private static Dictionary<int, StockFileDetail> LotsItemDetails;
            private static List<Guid> StkIDsWithDefaultPDEsSet;
            private static List<Guid> StkPDEIDDefaultUpdates;
            private static Dictionary<Int64, Tuple<Guid, int>> StockAPNs;
            private static Dictionary<Guid, List<String>> StockAPNsByID;
            private static Dictionary<Tuple<int, string>, Tuple<Guid, Guid>> StockPDEs;
            private static Dictionary<Guid, string> StockNames;
            private static Dictionary<int, Guid> ExistingStkNumbers;
            public static Dictionary<int, StockFileDetail> ConvertStockFile(string lotsConnectionString, string cpiamConnectionString, List<SupplierItemCountMap> SupplierMap,
            Dictionary<int, Tuple<int, String>> LotsSupplierReallocations, Dictionary<int, int> LotsDepartmentReallocations, Dictionary<int, string> LotsCategoryIDToNameMap)
            {
                LotsConnectionString = lotsConnectionString;
                CpiamConnectionString = cpiamConnectionString;
                LotsItemDetails = new Dictionary<int, StockFileDetail>();
                StkIDsWithDefaultPDEsSet = new List<Guid>();
                StkPDEIDDefaultUpdates = new List<Guid>();
                //Stock file look up codes - if not numeric then save as a PDE and assign new stock number
                //Set up batching datatables
                DataTable AddedStockFilesDT = DataTables.CreateStockDT();
                DataTable SuppliersDT = DataTables.CreateSuppliersDT();
                //create a temporary updated stock files table - if it doesnt exist
                StockFile.CreateTempUpdateStockTable(CpiamConnectionString, TempUpdatedStockTableName);
                DataTable UpdatedStockFilesDT = DataTables.CreateUpdateStockDT();
                DataTable StockFileTurnsDT = DataTables.CreateStockTurnsDT();
                DataTable StockFileTransDT = DataTables.CreateStockTransDT();
                DataTable StockFileHistTotDT = DataTables.CreateStockHistTotDT();
                DataTable StockFileHistLYDT = DataTables.CreateStockHistLYDT();
                DataTable StockFileHistCurrDT = DataTables.CreateStockHistCurrDT();
                DataTable StockFilePDEDT = DataTables.CreateStockPDEDT();
                DataTable StockFileAPNDT = DataTables.CreateStockAPNDT();
                DataTable StockFilePromotionsDT = DataTables.CreateStockPromotionsDT();
                DataTable StockFileLocCatDT = DataTables.CreateStockFileLocCatDT();

                try
                {
                    SupplierDictionary = new Dictionary<int, int>();
                    foreach (SupplierItemCountMap supplierMap in SupplierMap)
                    {
                        SupplierDictionary[supplierMap.LotsSupplierID] = supplierMap.CPIAMSupplier.SupplierID;
                    }
                    ExistingStkNumbers = new Dictionary<int, Guid>();
                    int maxCPIAMNumber = StockFile.PopulateStkNumber(CpiamConnectionString, ExistingStkNumbers);
                    HashSet<Guid> MatchedSystemStockFiles = new HashSet<Guid>();
                    HashSet<int> UsedStockNumbers = new HashSet<int>();

                    //any stock numbers match the new Lots stock number - new Lots stock number based on whether it was numeric - if not get a new one
                    //1. reallocate all Lots stock numbers - anything starting with '0' OR not parseInt OR len > 7 needs a new stock number - existing number needs to be stored as a pde
                    //2. any stock numbers in cpiam pos max retail which is the same as the reallocated number needs to be changed
                    //something to store the existing Lots stock number and the newly allocated stock number
                    LotsReallocations = new Dictionary<string, int>();
                    //something to store the stock numbers in CPIAM POS Max that needs to be changed - so no stock numbers clash with the 
                    CPIAMReallocations = new Dictionary<Guid, int>();
                    LotsItemIDToCPIAMIDMap = new Dictionary<int, Tuple<Guid, int>>();

                    int maxLotsNumber = GetMaxLotsLookupNumber(LotsConnectionString);
                    SetMaxStockNumber(Math.Max(maxCPIAMNumber, maxLotsNumber));

                    //TODO: renv - put these in common CPIAM code
                    StockAPNs = new Dictionary<long, Tuple<Guid, int>>();
                    StockAPNsByID = new Dictionary<Guid, List<string>>();
                    StockFileApns.PopulateStockAPNs(CpiamConnectionString, StockAPNs, StockAPNsByID);
                    StockPDEs = new Dictionary<Tuple<int, string>, Tuple<Guid, Guid>>();
                    StockFilePDES.PopulateStockPDEs(CpiamConnectionString, StockPDEs);
                    StockNames = new Dictionary<Guid, string>();
                    StockFile.PopulateStockNames(CpiamConnectionString, StockNames);

                    PopulatePLUsupplierIDMap(LotsConnectionString);
                    PopulateLotsAPNs(LotsConnectionString);
                    PopulateLotsPDEs(LotsConnectionString);
                    PopulateOrderItemHistory(LotsConnectionString);
                    PopulateTransactionItemHistory(LotsConnectionString);
                    //PopulateOrderItemHistory(LOTSConnectionStr);
                    //PopulateTransactionItemHistory(LOTSConnectionStr);
                    //PopulateSupplierItemMinOrder(LOTSConnectionStr);

                    OleDbConnection dbConn = new OleDbConnection(LotsConnectionString);
                    dbConn.Open();
                    int matches = 0;
                    int nonMatches = 0;
                    using (OleDbCommand command = dbConn.CreateCommand())
                    {
                        command.CommandText = string.Format(@"SELECT Stock.StockID, Stock.TradeName, Stock.ProductGroupID, Stock.ProductClassID, Stock.SOH, Stock.PackSize, Stock.Retail, Stock.Cost, Stock.RealCost, 
                         Stock.AverageCost, Stock.DateCreated, Stock.Reorder, Stock.NoDiscount, Stock.Manufacturer, GSTGroup.GSTGroupValue, GSTType.GSTTypeName, Stock.Shelflabel, Stock.MinOrdQty, Stock.MinimumSOH, ProductClass.ProductClassName, Stock.PLU
                         FROM (((Stock INNER JOIN
                         GSTGroup ON Stock.GSTGroupID = GSTGroup.GSTGroupID) INNER JOIN
                         GSTType ON Stock.GSTTypeID = GSTType.GSTTypeID) LEFT OUTER JOIN
                         ProductClass ON Stock.ProductClassID = ProductClass.ProductClassID)
                         WHERE (Stock.StockID > 0) AND (NOT (Stock.TradeName LIKE '.%'))
                         ORDER BY Stock.TradeName");
                        OleDbDataReader reader = command.ExecuteReader();
                        DateTime start = DateTime.Now;
                        List<StockDetails> lotsItems = new List<StockDetails>();
                        while (reader.Read())
                        {
                            StockDetails item = new StockDetails();
                            int i = 0;
                            item.StockID = reader.IsDBNull(i) ? 0 : reader.GetInt32(i);
                            item.TradeName = reader.IsDBNull(++i) ? null : reader.GetString(i);
                            item.ProductGroupID = reader.IsDBNull(++i) ? 0 : reader.GetInt32(i);
                            item.ProductClassID = reader.IsDBNull(++i) ? 0 : reader.GetInt32(i);
                            item.SOH = reader.IsDBNull(++i) ? 0 : reader.GetFloat(i);
                            item.PackSize = reader.IsDBNull(++i) ? 0 : reader.GetFloat(i);
                            item.Retail = reader.IsDBNull(++i) ? 0 : reader.GetInt32(i);
                            item.Cost = reader.IsDBNull(++i) ? 0 : reader.GetInt32(i);
                            item.RealCost = reader.IsDBNull(++i) ? 0 : reader.GetInt32(i);
                            item.AverageCost = reader.IsDBNull(++i) ? 0 : reader.GetInt32(i);
                            item.DateCreated = reader.IsDBNull(++i) ? DateTime.Now : reader.GetDateTime(i);
                            item.Reorder = reader.IsDBNull(++i) ? false : reader.GetBoolean(i);
                            item.NoDiscount = reader.IsDBNull(++i) ? false : reader.GetBoolean(i);
                            item.Manufacturer = reader.IsDBNull(++i) ? 0 : reader.GetInt32(i);
                            item.GSTGroupValue = reader.IsDBNull(++i) ? 0 : reader.GetFloat(i);
                            item.GSTTypeName = reader.IsDBNull(++i) ? null : reader.GetString(i);
                            item.Shelflabel = reader.IsDBNull(++i) ? false : reader.GetBoolean(i);
                            item.MinOrdQty = reader.IsDBNull(++i) ? 0 : reader.GetInt32(i);
                            item.MinimumSOH = reader.IsDBNull(++i) ? 0 : reader.GetInt32(i);
                            item.ProductClassName = reader.IsDBNull(++i) ? null : reader.GetString(i);
                            item.PLU = reader.IsDBNull(++i) ? null : reader.GetString(i);
                            lotsItems.Add(item);

                            bool reallocRequired = false;
                            int stockNumberToBeUsed = item.StockID;
                            if (item.StockID > 999999 || (UsedStockNumbers.Contains(item.StockID)))
                            {
                                reallocRequired = true;
                                if (reallocRequired)
                                {
                                    int newNumber = GetNextStockNumber();
                                    LotsReallocations[item.StockID.ToString()] = newNumber;

                                    stockNumberToBeUsed = newNumber;
                                }
                            }

                            if (ExistingStkNumbers.ContainsKey(stockNumberToBeUsed))
                            {
                                int newNumber = GetNextStockNumber();
                                CPIAMReallocations[ExistingStkNumbers[stockNumberToBeUsed]] = newNumber;
                            }

                            UsedStockNumbers.Add(stockNumberToBeUsed);

                            //see if the stock file is a match
                            //1. APN matches and name similarity rating > 70%
                            //2. use pde/supplier combination and name similarity rating > 70%
                            //3. There is a match on PDE and APN
                            //TODO: renv - move to common class
                            //Guid? CpiamStkNo = GetCPIAMStockFileMatch(GetLotsApns(item.StockID), GetLotsPDEs(item.StockID), item.TradeName);
                            Guid? CpiamStkNo = StockFile.GetCPIAMStockFileMatch(GetLotsApns(item.StockID), GetLotsPDEs(item.StockID), item.TradeName, StockNames, StockAPNs, StockPDEs, SupplierDictionary);
                            //Only if it has not been matched before
                            if (CpiamStkNo.HasValue && !MatchedSystemStockFiles.Contains(CpiamStkNo.Value))
                            {
                                SaveItem(item, true, CpiamStkNo.Value, AddedStockFilesDT, UpdatedStockFilesDT, LotsSupplierReallocations, LotsDepartmentReallocations, LotsCategoryIDToNameMap, StockFileTurnsDT,
                                    StockFileTransDT, StockFileHistTotDT, StockFileHistLYDT, StockFileHistCurrDT, StockFilePDEDT, StockFileAPNDT, StockFileLocCatDT, StockFilePromotionsDT, SuppliersDT);

                                MatchedSystemStockFiles.Add(CpiamStkNo.Value);
                                matches++;
                            }
                            else
                            {
                                SaveItem(item, false, null, AddedStockFilesDT, UpdatedStockFilesDT, LotsSupplierReallocations, LotsDepartmentReallocations, LotsCategoryIDToNameMap, StockFileTurnsDT,
                                    StockFileTransDT, StockFileHistTotDT, StockFileHistLYDT, StockFileHistCurrDT, StockFilePDEDT, StockFileAPNDT, StockFileLocCatDT, StockFilePromotionsDT, SuppliersDT);
                                nonMatches++;
                            }

                            if ((AddedStockFilesDT.Rows.Count + UpdatedStockFilesDT.Rows.Count) % BATCH_SAVE_SIZE == 0)
                            {
                                //MessageBox.Show("PDEs size: " + StkIDsWithDefaultPDEsSet.Count);
                                SaveAllDataTables(CpiamConnectionString, AddedStockFilesDT, UpdatedStockFilesDT, StockFileTurnsDT, StockFileTransDT, StockFileHistTotDT, StockFileHistLYDT, StockFileHistCurrDT, StockFilePDEDT, StockFileAPNDT, StockFileLocCatDT, StockFilePromotionsDT, SuppliersDT);
                            }
                        }
                                /*
                            catch (Exception ex)
                            {
                                Logger.LogWarningAndError("Error in Convert Stock ", ex);
                            }
                            */
                            
                        //}
                        SaveAllDataTables(CpiamConnectionString, AddedStockFilesDT, UpdatedStockFilesDT, StockFileTurnsDT, StockFileTransDT, StockFileHistTotDT, StockFileHistLYDT, StockFileHistCurrDT, StockFilePDEDT, StockFileAPNDT, StockFileLocCatDT, StockFilePromotionsDT, SuppliersDT);
                    }
                    dbConn.Close();
                }
                catch (Exception ex)
                {
                    Logger.LogWarningAndError("Error in Convert Stock ", ex);
                    MessageBox.Show("Error converting Stock: " + ex.Message + "\n\nStackTrace=" + ex.StackTrace);
                }
                return LotsItemDetails;
            }


            private static void SaveAllDataTables(String cpiamConnectionString, DataTable addedStockFilesDT, DataTable updatedStockFilesDT, DataTable stockFileTurnsDT,
                DataTable stockFileTransDT, DataTable stockFileHistTotDT, DataTable stockFileHistLYDT, DataTable stockFileHistCurrDT, DataTable stockFilePDEDT, DataTable stockFileAPNDT,
                DataTable stockFileLocCatDT, DataTable stockFilePromotionsDT, DataTable suppliersDT)
            {
                //CPIAMReallocations - before saving update and existing stock files which need reallocations
                //1 - perform reallocation of existing stock numbers - CPIAMReallocations
                //2 - anything in updatedStockFilesDT - write to a temp table in bulk, perform update in query. Delete all references tables except PDE, APN (reallocation should have been performed already)
                //3 - write all other tables including addedStockFilesDT

                if (CPIAMReallocations.Count > 0)
                {
                    StockFile.CreateTempReallocationStockTable(cpiamConnectionString, TempReallocationsStockTableName);
                    DataTable reallocatedStkNumbersDT = DataTables.CreateReallocatedStockFileDT();
                    foreach (Guid stkID in CPIAMReallocations.Keys)
                    {
                        reallocatedStkNumbersDT.Rows.Add(stkID, CPIAMReallocations[stkID]);
                    }
                    dbSaveBatch.SaveBatch(reallocatedStkNumbersDT, TempReallocationsStockTableName, cpiamConnectionString);
                    //perform update query
                    StockFile.UpdateStockFileNumbers(cpiamConnectionString);
                    CPIAMReallocations.Clear();
                }

                if (updatedStockFilesDT.Rows.Count > 0)
                {
                    //delete all references tables with stockid inupdatedStockFilesDT
                    StringBuilder inSB = new StringBuilder();
                    foreach (DataRow row in updatedStockFilesDT.Rows)
                    {
                        if (inSB.Length == 0)
                        {
                            inSB.Append("('" + row["StkID"].ToString() + "'");
                        }
                        else
                            inSB.Append(",'" + row["StkID"].ToString() + "'");
                    }
                    inSB.Append(")");
                    StockFile.CreateTempUpdateStockTable(cpiamConnectionString, TempUpdatedStockTableName);
                    dbSaveBatch.SaveBatch(updatedStockFilesDT, TempUpdatedStockTableName, cpiamConnectionString);
                    StockFile.RemoveUpdatedStockFileNumberReferences(cpiamConnectionString, inSB.ToString());
                    StockFile.UpdateStockFiles(cpiamConnectionString);
                }

                //clear any default pdes where stkid in StkIDsWithDefaultPDEsSet
                if (StkIDsWithDefaultPDEsSet.Count > 0)
                {
                    StringBuilder inSB = new StringBuilder();
                    foreach (Guid id in StkIDsWithDefaultPDEsSet)
                    {
                        if (inSB.Length == 0)
                        {
                            inSB.Append("('" + id.ToString() + "'");
                        }
                        else
                            inSB.Append(",'" + id.ToString() + "'");
                    }
                    inSB.Append(")");

                    StockFile.ClearStockFilePDEsDefault(cpiamConnectionString, inSB.ToString());
                    StkIDsWithDefaultPDEsSet.Clear();
                }

                //update any system pdes where StkPDEID in StkPDEIDDefaultUpdates
                if (StkPDEIDDefaultUpdates.Count > 0)
                {
                    //StringBuilder inSB = new StringBuilder();
                    //foreach (Guid id in StkPDEIDDefaultUpdates)
                    //{
                    //    if (inSB.Length == 0)
                    //    {
                    //        inSB.Append("('" + id.ToString() + "'");
                    //    }
                    //    else
                    //        inSB.Append(",'" + id.ToString() + "'");
                    //}
                    //inSB.Append(")");

                    try
                    {
                        StockFile.SetStockFilePDEsDefault(cpiamConnectionString, StkPDEIDDefaultUpdates);
                        StkPDEIDDefaultUpdates.Clear();
                    }
                    catch (Exception ex)
                    {
                        MessageBox.Show("StkPDEIDDefaultUpdates:: count=" + StkPDEIDDefaultUpdates.Count + " exception. ex=" + ex.Message + "\nStacktrace=" + ex.StackTrace);
                        throw ex;
                    }
                }

                if (addedStockFilesDT.Rows.Count > 0)
                    dbSaveBatch.SaveBatch(addedStockFilesDT, "StockFile", cpiamConnectionString);

                if (stockFileTurnsDT.Rows.Count > 0)
                {
                    HashSet<Guid> stkIDs = new HashSet<Guid>();
                    foreach (DataRow row in stockFileTurnsDT.Rows)
                    {
                        if (stkIDs.Contains((Guid)row["StkID"]))
                        {
                            ;
                        }

                        else
                        {
                            stkIDs.Add((Guid)row["StkID"]);
                        }
                    }
                    dbSaveBatch.SaveBatch(stockFileTurnsDT, "StockFileTurns", cpiamConnectionString);
                }

                if (stockFileTransDT.Rows.Count > 0)
                    dbSaveBatch.SaveBatch(stockFileTransDT, "StockFileTrans", cpiamConnectionString);

                if (stockFileHistTotDT.Rows.Count > 0)
                    dbSaveBatch.SaveBatch(stockFileHistTotDT, "StockFileHistTot", cpiamConnectionString);

                if (stockFileHistLYDT.Rows.Count > 0)
                    dbSaveBatch.SaveBatch(stockFileHistLYDT, "StockFileHistLY", cpiamConnectionString);

                if (stockFileHistCurrDT.Rows.Count > 0)
                    dbSaveBatch.SaveBatch(stockFileHistCurrDT, "StockFileHistCurr", cpiamConnectionString);

                if (stockFilePDEDT.Rows.Count > 0)
                    dbSaveBatch.SaveBatch(stockFilePDEDT, "StockFilePDES", cpiamConnectionString);

                if (stockFileAPNDT.Rows.Count > 0)
                    dbSaveBatch.SaveBatch(stockFileAPNDT, "StockFileApns", cpiamConnectionString);

                if (stockFileLocCatDT.Rows.Count > 0)
                    dbSaveBatch.SaveBatch(stockFileLocCatDT, "StockFileLocCat", cpiamConnectionString);

                if (stockFilePromotionsDT.Rows.Count > 0)
                    dbSaveBatch.SaveBatch(stockFilePromotionsDT, "StockPromotions", cpiamConnectionString);

                if (suppliersDT.Rows.Count > 0)
                    dbSaveBatch.SaveBatch(suppliersDT, "Suppliers", cpiamConnectionString);
            }

            private static Supplier BlankSupplier = null;
            public static void SaveItem(StockDetails item, bool isUpdate, Guid? existingStkID, DataTable AddedStockFilesDT, DataTable UpdatedStockFilesDT, Dictionary<int, Tuple<int, String>> lotsSupplierReallocations, Dictionary<int, int> lotsDepartmentReallocations,
                                        Dictionary<int, string> lotsCategoryIDToNameMap, DataTable StockFileTurnsDT, DataTable StockFileTransDT, DataTable StockFileHistTotDT, DataTable StockFileHistLYDT, DataTable StockFileHistCurrDT, DataTable StockFilePDEDT, DataTable StockFileAPNDT,
                                        DataTable StockFileLocCatDT, DataTable StockFilePromotionsDT,DataTable SuppliersDT)
            {
                    Guid guidStkID = existingStkID.HasValue ? existingStkID.Value : Guid.NewGuid();
                    String strStockName = String.IsNullOrWhiteSpace(item.TradeName) ? string.Empty : item.TradeName.Trim();
                    short intPackSize = 1;
                    Boolean blnStockActive = false;
                    decimal decCost = 0;
                    decimal decAvgCost = 0;
                    string strGSTCode = "N";
                    decimal decGSTPerc = 0;
                    decimal decExTaxSell = 0;
                    decimal decGSTAmt = 0;
                    decimal decGSTIncSell = 0;
                    decimal decMUP = 0;
                    decimal dblSOH = 0;
                    DateTime dteCreated = DateTime.Now;
                    int intDeptID = 9999;
                    short shtMinStk = 0;
                    int intMinOrdQty = 0;
                    Boolean blnPrintLab = false;
                    Boolean blnOrdStock = false;
                    int intSupID = 0;

                    if (CurrentStockHistSales.ContainsKey(item.StockID) || (item.SOH!=0))
                    {
                        blnStockActive = true;
                    }

                    if (item.RealCost == 0)
                        decCost = item.Cost == 0 ? 0 : item.Cost / 100;
                    else
                    {
                        if (item.RealCost > 0)
                            decCost = item.RealCost / 100;
                        else
                            decCost = item.Cost == 0 ? 0 : item.Cost / 100;
                    }

                    if (item.AverageCost == 0)
                        decAvgCost = decCost;
                    else
                    {
                        decAvgCost = item.AverageCost > 0 ? item.AverageCost / 100 : decCost;
                    }

                    if (item.GSTTypeName == null)
                    {
                        strGSTCode = "F";
                        decGSTPerc = 0;
                    }
                    else
                    {
                        if (item.GSTTypeName == "Standard")
                        {
                            strGSTCode = "Y";
                            decGSTPerc = 10;
                        }
                        else
                        {
                            if (item.GSTGroupValue == 0)
                            {
                                strGSTCode = "F";
                                decGSTPerc = 0;
                            }
                            else
                            {
                                if (item.GSTGroupValue == 10)
                                {
                                    strGSTCode = "N";
                                    decGSTPerc = 0;
                                }
                                else
                                {
                                    strGSTCode = "F";
                                    decGSTPerc = 0;
                                }
                            }
                        }
                    }

                    if (item.Retail == 0)
                    {
                        decExTaxSell = 0;
                        decGSTAmt = 0;
                        decGSTIncSell = 0;
                        decMUP = 0;
                    }
                    else
                    {
                        if (item.Retail > 0)
                        {
                                    decGSTIncSell = item.Retail / 100;
                                    if(strGSTCode == "Y")
                                    {
                                        decGSTAmt = decGSTIncSell / 11;
                                        decExTaxSell = decGSTIncSell - decGSTAmt;
                                    }
                                    else
                                    {
                                        decGSTAmt = 0;
                                        decExTaxSell = decGSTIncSell;
                                    }
                                    if (decCost == 0)
                                    {
                                        decMUP = 0;
                                    }
                                    else
                                    {
                                        decMUP = ((decExTaxSell - decCost) / decCost) * 100;
                                    }

                        }
                        else
                        {
                            if (decCost == 0)
                            {
                                decExTaxSell = 0;
                                decGSTAmt = 0;
                                decGSTIncSell = 0;
                                decMUP = 0;
                            }
                            else
                            {
                                decExTaxSell = ((decCost * 0.55M) + decCost);
                                if (strGSTCode == "Y")
                                    decGSTAmt = decExTaxSell * 0.1M;
                                else
                                    decGSTAmt = 0;

                                decGSTIncSell = RoundToNearest5(decExTaxSell + decGSTAmt);
                                decMUP = ((decExTaxSell - decCost) / decCost) * 100;
                            }
                        }
                    }
                    if (item.PackSize != 0)
                    {
                        dblSOH = System.Convert.ToDecimal(item.SOH / item.PackSize);
                    }
                        dteCreated = item.DateCreated;

                        if(item.ProductGroupID==0)
                            intDeptID = 9999;
                        else
                            if(item.ProductGroupID == 0)
                                intDeptID = 9999;
                            else
                                intDeptID = item.ProductGroupID;
                        
                        shtMinStk = System.Convert.ToInt16(item.MinimumSOH);
                        intMinOrdQty = item.MinOrdQty;

                        if(item.Shelflabel==false)
                            blnPrintLab = false;
                        else
                            blnPrintLab = item.Shelflabel;
                        if (item.Reorder == false)
                            blnOrdStock = false;
                        else
                            blnOrdStock = item.Reorder;

                    //decimal decStockTakeFig = 0; //?
                    //DateTime dteLastStockTake = DateTime.Now; //?
                    //DateTime dteLastInvoice = DateTime.Now; //?
                    //DateTime dteLastSold = DateTime.Now;
                    //DateTime dteWSLast = new DateTime(1900, 1, 1);
                    //intDeptID = item.ProductGroupID != 0 && lotsDepartmentReallocations.ContainsKey(item.ProductGroupID) ? lotsDepartmentReallocations[item.ProductGroupID] : 9999;
                    intDeptID = item.ProductGroupID == 0 ? 9999 : item.ProductGroupID;
                    if (item.PLU != null & item.PLU != "" && PLUsupplierIDMAp.ContainsKey(item.PLU))
                        intSupID = PLUsupplierIDMAp[item.PLU];

                    if (lotsSupplierReallocations.ContainsKey(intSupID))
                        intSupID = lotsSupplierReallocations[intSupID].Item1;
                    decimal decAvgCostTotal = decAvgCost * dblSOH;
                    //String strLabType = "THA";
                    //String strStkManufCode = "ZZZ"; //?
                    string strMfc = item.Manufacturer.ToString(); 
                    decimal decPrice1 = item.Cost/100;//??
                    short decQty1 = 0;//?
                    decimal decPrice2 = 0;//?
                    short decQty2 = 0;//?
                    decimal decPrice3 = 0;//?
                    short decQty3 = 0;//?
                    decimal decPrice4 = 0;//?
                    short decQty4 = 0;//?
                    decimal decPrice5 = 0;//?
                    short decQty5 = 0;//?
                    bool blnS3 = false;//?
                    bool blnEthical = false;//?
                    bool blnDiscable = false;
                    String strExternPDE = item.PLU == null ? "0" : item.PLU; 

                    DateTime start = DateTime.Now;
                    DateTime end = DateTime.Now;
                    StockPromotions stockPromotions = new StockPromotions(guidStkID, start, end, 0, 0, "General Promotion");
                    StockPromotions.SaveToDateTable(stockPromotions, StockFilePromotionsDT); 
                    //StockFilePromotionsDT.Rows.Add(Guid.NewGuid(), guidStkID, start, end, 0, 0, "General Promotion");

                    //String strAPN = string.Empty;
                    //String strStockSettings = string.Empty;
                    //String strStkMaintCode = "Z"; //?

                    int stockNumber = 0;
                    if (LotsReallocations.ContainsKey(item.StockID.ToString()))
                        stockNumber = LotsReallocations[item.StockID.ToString()];
                    else
                    {
                        stockNumber = item.StockID;
                    }

                    if (isUpdate)
                        CPIAMReallocations[guidStkID] = stockNumber;

                    //current values on the stock file
                    decimal curCost = 0.0M;
                    float curPurQty = 0;
                    decimal curSaleVal = 0.0M;
                    float curSaleQty = 0;
                    decimal curCOGS = 0.0M;
                    if (CurrentStockPurchases.ContainsKey(item.StockID))
                    {
                        curPurQty = CurrentStockPurchases[item.StockID].PurQty;
                        curCost = CurrentStockPurchases[item.StockID].Cost;
                    }
                    if (CurrentStockSale.ContainsKey(item.StockID))
                    {
                        curSaleQty = CurrentStockSale[item.StockID].SoldQty;
                        curSaleVal = CurrentStockSale[item.StockID].SellVal;
                        curCOGS = CurrentStockSale[item.StockID].COGS;
                    }

                    //Stock file history total
                    decimal totalCost = 0.0M;
                    float totalPurQty = 0;
                    decimal totalSaleVal = 0.0M;
                    float totalSaleQty = 0;
                    decimal totalCOGS = 0.0M;
                    if (TotalStockPurchases.ContainsKey(item.StockID))
                    {
                        totalPurQty = TotalStockPurchases[item.StockID].PurQty;
                        totalCost = TotalStockPurchases[item.StockID].Cost;
                    }
                    if (TotalStockSales.ContainsKey(item.StockID))
                    {
                        totalSaleQty = TotalStockSales[item.StockID].SoldQty;
                        totalSaleVal = TotalStockSales[item.StockID].SellVal;
                        totalCOGS = TotalStockSales[item.StockID].COGS;
                    }
                    StockFileHistTotDT.Rows.Add(guidStkID, stockNumber, 0, 0.0M, 0.0M, 0, 0.0M, 0, 0.0M, 0, 0.0M,
                                        totalPurQty, totalCost, totalCOGS, totalSaleQty, totalSaleVal, 0, 0.0M);

                    //stockfilehistly
                    float purchaseQty1 = 0;
                    decimal purchaseCost1 = 0;
                    decimal COGS1 = 0;
                    float soldQty1 = 0;
                    decimal soldVal1 = 0;

                    float purchaseQty2 = 0;
                    decimal purchaseCost2 = 0;
                    decimal COGS2 = 0;
                    float soldQty2 = 0;
                    decimal soldVal2 = 0;

                    float purchaseQty3 = 0;
                    decimal purchaseCost3 = 0;
                    decimal COGS3 = 0;
                    float soldQty3 = 0;
                    decimal soldVal3 = 0;

                    float purchaseQty4 = 0;
                    decimal purchaseCost4 = 0;
                    decimal COGS4 = 0;
                    float soldQty4 = 0;
                    decimal soldVal4 = 0;

                    float purchaseQty5 = 0;
                    decimal purchaseCost5 = 0;
                    decimal COGS5 = 0;
                    float soldQty5 = 0;
                    decimal soldVal5 = 0;

                    float purchaseQty6 = 0;
                    decimal purchaseCost6 = 0;
                    decimal COGS6 = 0;
                    float soldQty6 = 0;
                    decimal soldVal6 = 0;

                    float purchaseQty7 = 0;
                    decimal purchaseCost7 = 0;
                    decimal COGS7 = 0;
                    float soldQty7 = 0;
                    decimal soldVal7 = 0;

                    float purchaseQty8 = 0;
                    decimal purchaseCost8 = 0;
                    decimal COGS8 = 0;
                    float soldQty8 = 0;
                    decimal soldVal8 = 0;

                    float purchaseQty9 = 0;
                    decimal purchaseCost9 = 0;
                    decimal COGS9 = 0;
                    float soldQty9 = 0;
                    decimal soldVal9 = 0;

                    float purchaseQty10 = 0;
                    decimal purchaseCost10 = 0;
                    decimal COGS10 = 0;
                    float soldQty10 = 0;
                    decimal soldVal10 = 0;

                    float purchaseQty11 = 0;
                    decimal purchaseCost11 = 0;
                    decimal COGS11 = 0;
                    float soldQty11 = 0;
                    decimal soldVal11 = 0;

                    float purchaseQty12 = 0;
                    decimal purchaseCost12 = 0;
                    decimal COGS12 = 0;
                    float soldQty12 = 0;
                    decimal soldVal12 = 0;

                    if (LYStockHistPurchases.ContainsKey(item.StockID))
                    {
                        purchaseQty1 = LYStockHistPurchases[item.StockID].PurMth1;
                        purchaseCost1 = LYStockHistPurchases[item.StockID].CostMth1;

                        purchaseQty2 = LYStockHistPurchases[item.StockID].PurMth2;
                        purchaseCost2 = LYStockHistPurchases[item.StockID].CostMth2;

                        purchaseQty3 = LYStockHistPurchases[item.StockID].PurMth3;
                        purchaseCost3 = LYStockHistPurchases[item.StockID].CostMth3;

                        purchaseQty4 = LYStockHistPurchases[item.StockID].PurMth4;
                        purchaseCost4 = LYStockHistPurchases[item.StockID].CostMth4;

                        purchaseQty5 = LYStockHistPurchases[item.StockID].PurMth5;
                        purchaseCost5 = LYStockHistPurchases[item.StockID].CostMth5;

                        purchaseQty6 = LYStockHistPurchases[item.StockID].PurMth6;
                        purchaseCost6 = LYStockHistPurchases[item.StockID].CostMth6;

                        purchaseQty7 = LYStockHistPurchases[item.StockID].PurMth7;
                        purchaseCost7 = LYStockHistPurchases[item.StockID].CostMth7;

                        purchaseQty8 = LYStockHistPurchases[item.StockID].PurMth8;
                        purchaseCost8 = LYStockHistPurchases[item.StockID].CostMth8;

                        purchaseQty9 = LYStockHistPurchases[item.StockID].PurMth9;
                        purchaseCost9 = LYStockHistPurchases[item.StockID].CostMth9;

                        purchaseQty10 = LYStockHistPurchases[item.StockID].PurMth10;
                        purchaseCost10 = LYStockHistPurchases[item.StockID].CostMth10;

                        purchaseQty11 = LYStockHistPurchases[item.StockID].PurMth11;
                        purchaseCost11 = LYStockHistPurchases[item.StockID].CostMth11;

                        purchaseQty12 = LYStockHistPurchases[item.StockID].PurMth12;
                        purchaseCost12 = LYStockHistPurchases[item.StockID].CostMth12;
                    }
                    if (LYStockHistSales.ContainsKey(item.StockID))
                    {
                        soldQty1 = LYStockHistSales[item.StockID].SoldMth1;
                        soldVal1 = LYStockHistSales[item.StockID].SellMth1;
                        COGS1 = LYStockHistSales[item.StockID].COGSMth1;

                        soldQty2 = LYStockHistSales[item.StockID].SoldMth2;
                        soldVal2 = LYStockHistSales[item.StockID].SellMth2;
                        COGS2 = LYStockHistSales[item.StockID].COGSMth2;

                        soldQty3 = LYStockHistSales[item.StockID].SoldMth3;
                        soldVal3 = LYStockHistSales[item.StockID].SellMth3;
                        COGS3 = LYStockHistSales[item.StockID].COGSMth3;

                        soldQty4 = LYStockHistSales[item.StockID].SoldMth4;
                        soldVal4 = LYStockHistSales[item.StockID].SellMth4;
                        COGS4 = LYStockHistSales[item.StockID].COGSMth4;

                        soldQty5 = LYStockHistSales[item.StockID].SoldMth5;
                        soldVal5 = LYStockHistSales[item.StockID].SellMth5;
                        COGS5 = LYStockHistSales[item.StockID].COGSMth5;

                        soldQty6 = LYStockHistSales[item.StockID].SoldMth6;
                        soldVal6 = LYStockHistSales[item.StockID].SellMth6;
                        COGS6 = LYStockHistSales[item.StockID].COGSMth6;

                        soldQty7 = LYStockHistSales[item.StockID].SoldMth7;
                        soldVal7 = LYStockHistSales[item.StockID].SellMth7;
                        COGS7 = LYStockHistSales[item.StockID].COGSMth7;

                        soldQty8 = LYStockHistSales[item.StockID].SoldMth8;
                        soldVal8 = LYStockHistSales[item.StockID].SellMth8;
                        COGS8 = LYStockHistSales[item.StockID].COGSMth8;

                        soldQty9 = LYStockHistSales[item.StockID].SoldMth9;
                        soldVal9 = LYStockHistSales[item.StockID].SellMth9;
                        COGS9 = LYStockHistSales[item.StockID].COGSMth9;

                        soldQty10 = LYStockHistSales[item.StockID].SoldMth10;
                        soldVal10 = LYStockHistSales[item.StockID].SellMth10;
                        COGS10 = LYStockHistSales[item.StockID].COGSMth10;

                        soldQty11 = LYStockHistSales[item.StockID].SoldMth11;
                        soldVal11 = LYStockHistSales[item.StockID].SellMth11;
                        COGS11 = LYStockHistSales[item.StockID].COGSMth11;

                        soldQty12 = LYStockHistSales[item.StockID].SoldMth12;
                        soldVal12 = LYStockHistSales[item.StockID].SellMth12;
                        COGS12 = LYStockHistSales[item.StockID].COGSMth12;
                    }

                    StockFileHistLYDT.Rows.Add(guidStkID, stockNumber, 0, 0, 0, 0, 0, 0, 0, 0, 0,
                        purchaseQty1, purchaseCost1, COGS1, soldQty1, soldVal1, purchaseQty2, purchaseCost2, COGS2, soldQty2, soldVal2,
                        purchaseQty3, purchaseCost3, COGS3, soldQty3, soldVal3, purchaseQty4, purchaseCost4, COGS4, soldQty4, soldVal4,
                        purchaseQty5, purchaseCost5, COGS5, soldQty5, soldVal5, purchaseQty6, purchaseCost6, COGS6, soldQty6, soldVal6,
                        purchaseQty7, purchaseCost7, COGS7, soldQty7, soldVal7, purchaseQty8, purchaseCost8, COGS8, soldQty8, soldVal8,
                        purchaseQty9, purchaseCost9, COGS9, soldQty9, soldVal9, purchaseQty10, purchaseCost10, COGS10, soldQty10, soldVal10,
                        purchaseQty11, purchaseCost11, COGS11, soldQty11, soldVal11, purchaseQty12, purchaseCost12, COGS12, soldQty12, soldVal12);


                    //stockfilehistcurr
                    purchaseQty1 = 0;
                    purchaseCost1 = 0;
                    COGS1 = 0;
                    soldQty1 = 0;
                    soldVal1 = 0;

                    purchaseQty2 = 0;
                    purchaseCost2 = 0;
                    COGS2 = 0;
                    soldQty2 = 0;
                    soldVal2 = 0;

                    purchaseQty3 = 0;
                    purchaseCost3 = 0;
                    COGS3 = 0;
                    soldQty3 = 0;
                    soldVal3 = 0;

                    purchaseQty4 = 0;
                    purchaseCost4 = 0;
                    COGS4 = 0;
                    soldQty4 = 0;
                    soldVal4 = 0;

                    purchaseQty5 = 0;
                    purchaseCost5 = 0;
                    COGS5 = 0;
                    soldQty5 = 0;
                    soldVal5 = 0;

                    purchaseQty6 = 0;
                    purchaseCost6 = 0;
                    COGS6 = 0;
                    soldQty6 = 0;
                    soldVal6 = 0;

                    purchaseQty7 = 0;
                    purchaseCost7 = 0;
                    COGS7 = 0;
                    soldQty7 = 0;
                    soldVal7 = 0;

                    purchaseQty8 = 0;
                    purchaseCost8 = 0;
                    COGS8 = 0;
                    soldQty8 = 0;
                    soldVal8 = 0;

                    purchaseQty9 = 0;
                    purchaseCost9 = 0;
                    COGS9 = 0;
                    soldQty9 = 0;
                    soldVal9 = 0;

                    purchaseQty10 = 0;
                    purchaseCost10 = 0;
                    COGS10 = 0;
                    soldQty10 = 0;
                    soldVal10 = 0;

                    purchaseQty11 = 0;
                    purchaseCost11 = 0;
                    COGS11 = 0;
                    soldQty11 = 0;
                    soldVal11 = 0;

                    purchaseQty12 = 0;
                    purchaseCost12 = 0;
                    COGS12 = 0;
                    soldQty12 = 0;
                    soldVal12 = 0;

                    if (CurrentStockHistPurchases.ContainsKey(item.StockID))
                    {
                        purchaseQty1 = CurrentStockHistPurchases[item.StockID].PurMth1;
                        purchaseCost1 = CurrentStockHistPurchases[item.StockID].CostMth1;

                        purchaseQty2 = CurrentStockHistPurchases[item.StockID].PurMth2;
                        purchaseCost2 = CurrentStockHistPurchases[item.StockID].CostMth2;

                        purchaseQty3 = CurrentStockHistPurchases[item.StockID].PurMth3;
                        purchaseCost3 = CurrentStockHistPurchases[item.StockID].CostMth3;

                        purchaseQty4 = CurrentStockHistPurchases[item.StockID].PurMth4;
                        purchaseCost4 = CurrentStockHistPurchases[item.StockID].CostMth4;

                        purchaseQty5 = CurrentStockHistPurchases[item.StockID].PurMth5;
                        purchaseCost5 = CurrentStockHistPurchases[item.StockID].CostMth5;

                        purchaseQty6 = CurrentStockHistPurchases[item.StockID].PurMth6;
                        purchaseCost6 = CurrentStockHistPurchases[item.StockID].CostMth6;

                        purchaseQty7 = CurrentStockHistPurchases[item.StockID].PurMth7;
                        purchaseCost7 = CurrentStockHistPurchases[item.StockID].CostMth7;

                        purchaseQty8 = CurrentStockHistPurchases[item.StockID].PurMth8;
                        purchaseCost8 = CurrentStockHistPurchases[item.StockID].CostMth8;

                        purchaseQty9 = CurrentStockHistPurchases[item.StockID].PurMth9;
                        purchaseCost9 = CurrentStockHistPurchases[item.StockID].CostMth9;

                        purchaseQty10 = CurrentStockHistPurchases[item.StockID].PurMth10;
                        purchaseCost10 = CurrentStockHistPurchases[item.StockID].CostMth10;

                        purchaseQty11 = CurrentStockHistPurchases[item.StockID].PurMth11;
                        purchaseCost11 = CurrentStockHistPurchases[item.StockID].CostMth11;

                        purchaseQty12 = CurrentStockHistPurchases[item.StockID].PurMth12;
                        purchaseCost12 = CurrentStockHistPurchases[item.StockID].CostMth12;
                    }
                    if (CurrentStockHistSales.ContainsKey(item.StockID))
                    {
                        soldQty1 = CurrentStockHistSales[item.StockID].SoldMth1;
                        soldVal1 = CurrentStockHistSales[item.StockID].SellMth1;
                        COGS1 = CurrentStockHistSales[item.StockID].COGSMth1;

                        soldQty2 = CurrentStockHistSales[item.StockID].SoldMth2;
                        soldVal2 = CurrentStockHistSales[item.StockID].SellMth2;
                        COGS2 = CurrentStockHistSales[item.StockID].COGSMth2;

                        soldQty3 = CurrentStockHistSales[item.StockID].SoldMth3;
                        soldVal3 = CurrentStockHistSales[item.StockID].SellMth3;
                        COGS3 = CurrentStockHistSales[item.StockID].COGSMth3;

                        soldQty4 = CurrentStockHistSales[item.StockID].SoldMth4;
                        soldVal4 = CurrentStockHistSales[item.StockID].SellMth4;
                        COGS4 = CurrentStockHistSales[item.StockID].COGSMth4;

                        soldQty5 = CurrentStockHistSales[item.StockID].SoldMth5;
                        soldVal5 = CurrentStockHistSales[item.StockID].SellMth5;
                        COGS5 = CurrentStockHistSales[item.StockID].COGSMth5;

                        soldQty6 = CurrentStockHistSales[item.StockID].SoldMth6;
                        soldVal6 = CurrentStockHistSales[item.StockID].SellMth6;
                        COGS6 = CurrentStockHistSales[item.StockID].COGSMth6;

                        soldQty7 = CurrentStockHistSales[item.StockID].SoldMth7;
                        soldVal7 = CurrentStockHistSales[item.StockID].SellMth7;
                        COGS7 = CurrentStockHistSales[item.StockID].COGSMth7;

                        soldQty8 = CurrentStockHistSales[item.StockID].SoldMth8;
                        soldVal8 = CurrentStockHistSales[item.StockID].SellMth8;
                        COGS8 = CurrentStockHistSales[item.StockID].COGSMth8;

                        soldQty9 = CurrentStockHistSales[item.StockID].SoldMth9;
                        soldVal9 = CurrentStockHistSales[item.StockID].SellMth9;
                        COGS9 = CurrentStockHistSales[item.StockID].COGSMth9;

                        soldQty10 = CurrentStockHistSales[item.StockID].SoldMth10;
                        soldVal10 = CurrentStockHistSales[item.StockID].SellMth10;
                        COGS10 = CurrentStockHistSales[item.StockID].COGSMth10;

                        soldQty11 = CurrentStockHistSales[item.StockID].SoldMth11;
                        soldVal11 = CurrentStockHistSales[item.StockID].SellMth11;
                        COGS11 = CurrentStockHistSales[item.StockID].COGSMth11;

                        soldQty12 = CurrentStockHistSales[item.StockID].SoldMth12;
                        soldVal12 = CurrentStockHistSales[item.StockID].SellMth12;
                        COGS12 = CurrentStockHistSales[item.StockID].COGSMth12;
                    }
                    StockFileHistCurrDT.Rows.Add(guidStkID, stockNumber, 0, 0, 0, 0, 0, 0, 0, 0, 0,
                        purchaseQty1, purchaseCost1, COGS1, soldQty1, soldVal1, purchaseQty2, purchaseCost2, COGS2, soldQty2, soldVal2,
                        purchaseQty3, purchaseCost3, COGS3, soldQty3, soldVal3, purchaseQty4, purchaseCost4, COGS4, soldQty4, soldVal4,
                        purchaseQty5, purchaseCost5, COGS5, soldQty5, soldVal5, purchaseQty6, purchaseCost6, COGS6, soldQty6, soldVal6,
                        purchaseQty7, purchaseCost7, COGS7, soldQty7, soldVal7, purchaseQty8, purchaseCost8, COGS8, soldQty8, soldVal8,
                        purchaseQty9, purchaseCost9, COGS9, soldQty9, soldVal9, purchaseQty10, purchaseCost10, COGS10, soldQty10, soldVal10,
                        purchaseQty11, purchaseCost11, COGS11, soldQty11, soldVal11, purchaseQty12, purchaseCost12, COGS12, soldQty12, soldVal12);

                /*
                    StockFileHistTot stockFileHistTot = new StockFileHistTot(guidStkID, stockNumber);
                    StockFileHistTot.SaveToDateTable(stockFileHistTot, StockFileHistTotDT);

                    StockFileHistLY stockFileHistLY = new StockFileHistLY(guidStkID, stockNumber);
                    StockFileHistLY.SaveToDateTable(stockFileHistLY, StockFileHistLYDT);

                    StockFileHistCurr stockFileHistCurr = new StockFileHistCurr(guidStkID, stockNumber);
                    StockFileHistCurr.SaveToDateTable(stockFileHistCurr, StockFileHistCurrDT);
                */

                    if (isUpdate)
                    {
                        StockFile stockfile = new StockFile(guidStkID, stockNumber, strStockName, strMfc, intDeptID,
                                                             decGSTPerc, decPrice1, decQty1, decPrice2, decQty2,
                                                             decPrice3, decQty3, decPrice4, decQty4, decPrice5, decQty5, strExternPDE, intSupID,
                                                             dblSOH, shtMinStk, intMinOrdQty, blnStockActive, blnEthical, blnPrintLab, blnS3,
                                                             blnOrdStock, decCost, decCost, decAvgCost, decAvgCostTotal,
                                                             decCost, strGSTCode, decGSTPerc, decMUP, decExTaxSell, decGSTAmt, decGSTIncSell, intPackSize, 
                                                             blnDiscable, curPurQty, curCost, 0, 0, curSaleQty, curSaleVal, curCOGS);
                        StockFile.SaveToDateTable(stockfile, UpdatedStockFilesDT);

                    }
                    else
                    {
                        StockFile stockfile = new StockFile(guidStkID, stockNumber, strStockName, strMfc, intDeptID,
                                                             decGSTPerc, decPrice1, decQty1, decPrice2, decQty2,
                                                             decPrice3, decQty3, decPrice4, decQty4, decPrice5, decQty5, strExternPDE, intSupID,
                                                             dblSOH, shtMinStk, intMinOrdQty, blnStockActive, blnEthical, blnPrintLab, blnS3,
                                                             blnOrdStock, decCost, decCost, decAvgCost, decAvgCostTotal,
                                                             decCost, strGSTCode, decGSTPerc, decMUP, decExTaxSell, decGSTAmt, decGSTIncSell, intPackSize, 
                                                             blnDiscable, curPurQty, curCost, 0, 0, curSaleQty, curSaleVal, curCOGS);
                        StockFile.SaveToDateTable(stockfile, AddedStockFilesDT);
                    }

                    LotsItemIDToCPIAMIDMap[item.StockID] = new Tuple<Guid, int>(guidStkID, stockNumber);

                    StockFileDetail detail = new StockFileDetail(stockNumber, intDeptID, strGSTCode, strStockName);
                    LotsItemDetails[item.StockID] = detail;

                    StockFileTurns stockFileTurns = new StockFileTurns(guidStkID, stockNumber);
                    StockFileTurns.SaveToDateTable(stockFileTurns, StockFileTurnsDT);
                    StockFileTrans stockFileTrans = new StockFileTrans(guidStkID, stockNumber);
                    StockFileTrans.SaveToDateTable(stockFileTrans, StockFileTransDT);
                    //StockFileTurnsDT.Rows.Add(guidStkID, stockNumber);
                    //StockFileTransDT.Rows.Add(guidStkID, stockNumber);

                    
                    //pdes - any pdes not in table need to be added
                    //14/04/2013 renv: add the PDE if it is a user item (and the PDE/supplier combo not already added), or it is a system item and PDE/supplier doesn't exist on THIS item
                    List<Tuple<int, string>> LotsPDEs = GetLotsPDEs(item.StockID);
                    HashSet<Tuple<int, string>> stkAddedPDEs = new HashSet<Tuple<int, string>>();  //set of PDEs that have been added (CPIAM SupplierID, PDE Code)
                    foreach (Tuple<int, string> LotsSupplierPDE in LotsPDEs)
                    {
                        int lotsSupplierID = LotsSupplierPDE.Item1;
                        if (!String.IsNullOrWhiteSpace(LotsSupplierPDE.Item2) && lotsSupplierReallocations.ContainsKey(lotsSupplierID))
                        {
                            int cpiamSupplierID = lotsSupplierReallocations[lotsSupplierID].Item1;
                            string pde = LotsSupplierPDE.Item2.Trim();
                            Tuple<int, string> newSupplierPDETup = new Tuple<int, string>(cpiamSupplierID, pde);

                            //add pde
                            bool isDefault = cpiamSupplierID == intSupID;

                            //get the system StkID/StkPDEID if it exists for this supplier/PDE
                            Tuple<Guid, Guid> systemPDETuple;
                            StockPDEs.TryGetValue(newSupplierPDETup, out systemPDETuple);

                            if (!isUpdate || (isUpdate && (systemPDETuple == null || systemPDETuple.Item1 != guidStkID)))
                            {
                                //check if supplier/PDE already added by conversion
                                if (!stkAddedPDEs.Contains(newSupplierPDETup))
                                {
                                    Guid stkPDEID = Guid.NewGuid();
                                    StockFilePDES stockFilePDES = new StockFilePDES(stkPDEID, guidStkID, stockNumber, pde, cpiamSupplierID, strStockName, isDefault);
                                    StockFilePDES.SaveToDateTable(stockFilePDES, StockFilePDEDT);

                                    stkAddedPDEs.Add(newSupplierPDETup);

                                    //if default store to clear  all stock file pdes 
                                    if (isDefault && !StkIDsWithDefaultPDEsSet.Contains(guidStkID))
                                    {
                                        StkIDsWithDefaultPDEsSet.Add(guidStkID);
                                        StkPDEIDDefaultUpdates.Add(stkPDEID);
                                    }
                                }
                            }
                            else if (isUpdate && systemPDETuple != null && systemPDETuple.Item1 == guidStkID)
                            {
                                //we want to update an existing entry - only thing to update is whether to set the default or remove default 
                                if (isDefault && !StkIDsWithDefaultPDEsSet.Contains(guidStkID))
                                {
                                    StkIDsWithDefaultPDEsSet.Add(guidStkID);
                                    StkPDEIDDefaultUpdates.Add(systemPDETuple.Item2);
                                }
                            }
                        }
                        else if (!lotsSupplierReallocations.ContainsKey(lotsSupplierID))
                        {
                            //Logger.Warn("Setting PDEs for Item. Supplier does not exist: " + lotsSupplierID);
                        }
                    }

                    //apns - any apns not in table need to be added
                    if (LotsApns.ContainsKey(item.StockID))
                    {
                        List<String> Apns = LotsApns[item.StockID];
                        Int64 test = 0;
                        foreach (String apn in Apns)
                        {
                            //if numeric then add if it doesnt exist
                            if (Int64.TryParse(apn, out test))
                            {
                                //if it is in dict then check if apn exists otherwise add it
                                //string apnTrimmed = apn.Trim();
                                string apnTrimmed = test.ToString();
                                if (!StockAPNsByID.ContainsKey(guidStkID) || !StockAPNsByID[guidStkID].Contains(apnTrimmed))
                                {
                                    //add apn
                                    //StockFileAPNDT.Rows.Add(Guid.NewGuid(), guidStkID, stockNumber, apnTrimmed);
                                    StockFileApns stockFileApns = new StockFileApns(Guid.NewGuid(), guidStkID, stockNumber, apnTrimmed);
                                    StockFileApns.SaveToDateTable(stockFileApns, StockFileAPNDT);
                                }
                            }
                            else if (!String.IsNullOrWhiteSpace(apn))
                            {        
                                //create a supplier
                                if (BlankSupplier == null)
                                {
                                    Supplier supplier = new Supplier();
                                    supplier.SupplierID = SuppliersConverter.GetNextSupplierNumber();
                                    supplier.SupplierName = "Lots Conversion Supplier";
                                    SuppliersConverter.SaveSuppliers(supplier, SuppliersDT, true);
                                    BlankSupplier = supplier;
                                }                               
                                //add pde
                                //StockFilePDEDT.Rows.Add(Guid.NewGuid(), guidStkID, stockNumber, apn.Trim(), BlankSupplier.SupplierID, strStockName, false, false, false);
                                StockFilePDES stockFilePDES = new StockFilePDES(Guid.NewGuid(), guidStkID, stockNumber, apn.Trim(), BlankSupplier.SupplierID, strStockName, false);
                                StockFilePDES.SaveToDateTable(stockFilePDES, StockFilePDEDT);
                            }
                        }
                    }

                    //also need to add the non numeric lookup codes and barcodes
                    if (LotsReallocations.ContainsKey(item.StockID.ToString()))
                    {         
                        //create a supplier
                        if (BlankSupplier == null)
                        {
                            Supplier supplier = new Supplier();
                            supplier.SupplierID = SuppliersConverter.GetNextSupplierNumber();
                            supplier.SupplierName = "Lots Conversion Supplier";
                            SuppliersConverter.SaveSuppliers(supplier, SuppliersDT, true);
                            BlankSupplier = supplier;
                        }                       
                        //StockFilePDEDT.Rows.Add(Guid.NewGuid(), guidStkID, stockNumber, item.StockID, BlankSupplier.SupplierID, strStockName,false, false, false);
                        StockFilePDES stockFilePDES = new StockFilePDES(Guid.NewGuid(), guidStkID, stockNumber, item.StockID.ToString(), BlankSupplier.SupplierID, strStockName, false);
                        StockFilePDES.SaveToDateTable(stockFilePDES, StockFilePDEDT);
                    }
                        //StockFileLocCatDT.Rows.Add(Guid.NewGuid(), guidStkID, item.ProductClassName, false);
                        StockFileLocCat stockFileLocCat = new StockFileLocCat(Guid.NewGuid(), guidStkID, item.ProductClassName, false);
                        StockFileLocCat.SaveToDateTable(stockFileLocCat, StockFileLocCatDT);
                        //before any of this is done update all Stock Files requiring a new Stock Number - the ones that clash
            }

        public static HashSet<int> ExistingLookupCodeNumbers;
        public static int GetMaxLotsLookupNumber(String ConnectionString)
        {
            ExistingLookupCodeNumbers = new HashSet<int>();
            int maxInt = 0;
            OleDbConnection dbConn = new OleDbConnection(ConnectionString);
                dbConn.Open();
                using (OleDbCommand command = dbConn.CreateCommand())
                {
                    command.CommandText = string.Format(@"SELECT StockID FROM Stock");

                    OleDbDataReader reader = command.ExecuteReader();

                    while (reader.Read())
                    {
                            int lookupCode = reader.GetInt32(0);
                            if (!ExistingLookupCodeNumbers.Contains(lookupCode))
                                ExistingLookupCodeNumbers.Add(lookupCode);
                            if (lookupCode > maxInt)
                                maxInt = lookupCode;
                    }
                }

                dbConn.Close();

            return maxInt;
        }

        private static int MAX_EXISTING_NUMBER = 0;
        private static int currentStockNumber = 0;
        public static void SetMaxStockNumber(int number)
        {
            MAX_EXISTING_NUMBER = number;
            currentStockNumber = MAX_EXISTING_NUMBER;
        }


        public static Dictionary<int, List<string>> LotsApns;
        public static void PopulateLotsAPNs(String LotsConnectionString)
        {
            LotsApns = new Dictionary<int, List<string>>();
            OleDbConnection dbConn = new OleDbConnection(LotsConnectionString);
                dbConn.Open();
                using (OleDbCommand command = dbConn.CreateCommand())
                {
                    command.CommandText = string.Format(@"SELECT StockID, Barcode FROM Barcode");
                    OleDbDataReader reader = command.ExecuteReader();
                    while (reader.Read())
                    {
                            int itemID = reader.GetInt32(0);
                            String apn = reader.IsDBNull(1) ? null : reader.GetString(1);
                            if (LotsApns.ContainsKey(itemID))
                                LotsApns[itemID].Add(apn);
                            else
                            {
                                List<String> apnList = new List<string>();
                                apnList.Add(apn);
                                LotsApns[itemID] = apnList;
                            }
                    }
                }
                dbConn.Close();
        }

        public static Dictionary<int, List<Tuple<int, string>>> LotsPDEs;
        public static void PopulateLotsPDEs(String LotsConnectionString)
        {
            //Item DB
            LotsPDEs = new Dictionary<int, List<Tuple<int, string>>>();
            OleDbConnection dbConn = new OleDbConnection(LotsConnectionString);
                dbConn.Open();
                using (OleDbCommand command = dbConn.CreateCommand())
                {
                    command.CommandText = string.Format(@"SELECT Partcode.StockID, Partcode.SupplierID, Partcode.PartCode 
                    FROM (Partcode INNER JOIN Supplier ON Partcode.SupplierID = Supplier.SupplierID)");

                    OleDbDataReader reader = command.ExecuteReader();

                    while (reader.Read())
                    {
                            int itemID = reader.GetInt32(0);
                            int supplierID = reader.GetInt32(1);
                            string pde = reader.IsDBNull(2) ? null : reader.GetString(2);
                            if (!String.IsNullOrWhiteSpace(pde))
                            {
                                if (LotsPDEs.ContainsKey(itemID))
                                    LotsPDEs[itemID].Add(new Tuple<int, string>(supplierID, pde));
                                else
                                {
                                    List<Tuple<int, string>> pdeList = new List<Tuple<int, string>>();
                                    pdeList.Add(new Tuple<int, string>(supplierID, pde));
                                    LotsPDEs[itemID] = pdeList;
                                }
                            }
                        }
                    }
                dbConn.Close();
        }

        public static int GetNextStockNumber()
        {
            currentStockNumber = currentStockNumber + 1;
            return currentStockNumber;
        }

        public static List<long> GetLotsApns(int LotsItemID)
        {
            List<long> apns = new List<long>();
            long test = 0;
            if (LotsApns.ContainsKey(LotsItemID))
            {
                foreach (string apn in LotsApns[LotsItemID])
                {
                    if (Int64.TryParse(apn, out test))
                        apns.Add(test);
                }
            }
            return apns;
        }

        public static List<Tuple<int, string>> GetLotsPDEs(int LotsItemID)
        {
            List<Tuple<int, string>> pdes = new List<Tuple<int, string>>();
            if (LotsPDEs.ContainsKey(LotsItemID))
            {
                foreach (Tuple<int, string> supplierPDE in LotsPDEs[LotsItemID])
                {
                    pdes.Add(supplierPDE);
                }
            }
            return pdes;
        }
/*
        public static Guid? GetCPIAMStockFileMatch(List<long> LotsApns, List<Tuple<int, string>> LotsSupplierPDEs, string LotsName)
        {
            float overallSim;
            bool numbersMatch;
            string stkName;
            //try find by APN
            HashSet<Guid> apnMatchStkIDs = new HashSet<Guid>();
            foreach (long apn in LotsApns)
            {
                if (StockAPNs.ContainsKey(apn))
                {
                    Guid stkID = StockAPNs[apn].Item1;
                    if (IsStockNameSimilar(stkID, LotsName, out overallSim, out numbersMatch, out stkName))
                    {
                        return stkID;
                    }
                    else
                    {
                        apnMatchStkIDs.Add(stkID);
                    }
                }
            }

            //find by PDE
            foreach (Tuple<int, string> LotsSupplierPDE in LotsSupplierPDEs)
            {
                if (SupplierDictionary.ContainsKey(LotsSupplierPDE.Item1))
                {
                    Tuple<int, string> supplierPDE = new Tuple<int, string>(SupplierDictionary[LotsSupplierPDE.Item1], LotsSupplierPDE.Item2);
                    if (StockPDEs.ContainsKey(supplierPDE))
                    {
                        Guid stkID = StockPDEs[supplierPDE].Item1;
                        if (apnMatchStkIDs.Contains(stkID))
                        {
                            //print out where name match is bad
                            IsStockNameSimilar(stkID, LotsName, out overallSim, out numbersMatch, out stkName);

                            if (overallSim < 0.5)
                            {
                                ;
                            }
                            return stkID;
                        }
                        else if (IsStockNameSimilar(stkID, LotsName, out overallSim, out numbersMatch, out stkName))
                        {
                            return stkID;
                        }
                        else
                        {
                            ;
                        }
                    }
                }
            }
            return null;
        }

        private static bool IsStockNameSimilar(Guid stkID, string LotsName, out float overallSim, out bool numbersMatch, out string stkName)
        {
            StockNames.TryGetValue(stkID, out stkName);
            
            if (string.IsNullOrWhiteSpace(stkName))
            {
                overallSim = 0;
                numbersMatch = false;

                return false;
            }

            //float overallSim;
            float firstWordSim;
            //bool numbersMatch;
            FuzzyWordMatcher.GetInstance().DetermineSimilarity(LotsName, stkName, true, out overallSim, out firstWordSim, out numbersMatch);
            return numbersMatch && overallSim > 0.7;    //take 70% similarity as good enough
        }
*/
        private static decimal RoundToNearest5(decimal unRoundedPrice)
        {
            decimal roundedPrice = unRoundedPrice;
            roundedPrice = Math.Round(unRoundedPrice * 20) / 20;
            return roundedPrice;
        }

        private static Dictionary<string, int> PLUsupplierIDMAp;
        public static void PopulatePLUsupplierIDMap(string LotsConnString)
        {
            PLUsupplierIDMAp = new Dictionary<string, int>();

            String query = @"select distinct PLU, supplierID from stock, partcode where partcode=PLU";
            OleDbConnection conn = null;
            OleDbDataReader reader = null;
            conn = new OleDbConnection(LotsConnString);
            conn.Open();

            OleDbCommand command = new OleDbCommand(query, conn);
            command.CommandTimeout = 3600;
            reader = command.ExecuteReader();
            while (reader.Read())
            {
                string PLU = reader.IsDBNull(0) ? null : reader.GetString(0);
                int supplierID = reader.IsDBNull(1) ? 0 : reader.GetInt32(1);
                if (PLU != null && PLU != "" && !PLUsupplierIDMAp.ContainsKey(PLU))
                {
                    PLUsupplierIDMAp[PLU] = supplierID;
                }
            }

            if (reader != null)
                reader.Close();

            if (conn != null)
                conn.Close();
        }

        private static Dictionary<int, HistSales> CurrentStockHistSales;
        private static Dictionary<int, HistSales> LYStockHistSales;
        private static Dictionary<int, Sale> CurrentStockSale;
        private static Dictionary<int, Sale> TotalStockSales;
        public static void PopulateTransactionItemHistory(String ConnectionString)
        {
            OleDbConnection dbConn = new OleDbConnection(ConnectionString);
            try
            {
                DateTime currentDateTime = DateTime.Now;

                DateTime startCurrentMonth = new DateTime(currentDateTime.Year, currentDateTime.Month, 1);
                DateTime startCurHistMonth = startCurrentMonth.AddYears(-1);
                DateTime startLastHistMonth = startCurHistMonth.AddYears(-1);

                CurrentStockHistSales = new Dictionary<int, HistSales>();
                LYStockHistSales = new Dictionary<int, HistSales>();
                CurrentStockSale = new Dictionary<int, Sale>();
                TotalStockSales = new Dictionary<int, Sale>();

                dbConn.Open();
                using (OleDbCommand command = dbConn.CreateCommand())
                {
                    command.CommandText = string.Format(@"SELECT History.StockID, History.[Month] AS MonthYear, SUM(History.QtySold) AS Qty, SUM(History.ValueSold) AS SaleValue, SUM(History.TotalCost), SUM(History.TotalTax)  AS TotalTax
                        FROM History
                        GROUP BY History.StockID, History.[Month]
                        ORDER BY History.[Month]");

                    OleDbDataReader reader = command.ExecuteReader();

                    while (reader.Read())
                    {
                        //decimal govCont = reader.GetDecimal(0);
                        int itemID = reader.GetInt32(0);
                        DateTime date = reader.GetDateTime(1);
                        double quantity = reader.GetDouble(2);
                        decimal price = (decimal)reader.GetDouble(3)/100;
                        decimal cost = (decimal)reader.GetDouble(4)/100;
                        decimal tax = (decimal)reader.GetDouble(5)/100;
                        decimal totalSaleVal = price * (decimal)quantity;
                        if (tax > 0)
                            totalSaleVal = totalSaleVal - tax;

                        //totalSaleVal += govCont;

                        if (date >= startCurrentMonth)
                        {
                            if (!CurrentStockSale.ContainsKey(itemID))
                                CurrentStockSale[itemID] = new Sale();

                            CurrentStockSale[itemID].SoldQty += (float)quantity;
                            CurrentStockSale[itemID].SellVal += totalSaleVal;
                            CurrentStockSale[itemID].COGS += cost;
                        }
                        else if (date >= startCurHistMonth)
                        {
                            //save to start
                            if (!CurrentStockHistSales.ContainsKey(itemID))
                                CurrentStockHistSales[itemID] = new HistSales();

                            if (date.Month == 1)
                            {
                                CurrentStockHistSales[itemID].SoldMth1 += (float)quantity;
                                CurrentStockHistSales[itemID].SellMth1 += totalSaleVal;
                                CurrentStockHistSales[itemID].COGSMth1 += cost;
                            }
                            else if (date.Month == 2)
                            {
                                CurrentStockHistSales[itemID].SoldMth2 += (float)quantity;
                                CurrentStockHistSales[itemID].SellMth2 += totalSaleVal;
                                CurrentStockHistSales[itemID].COGSMth2 += cost;
                            }
                            else if (date.Month == 3)
                            {
                                CurrentStockHistSales[itemID].SoldMth3 += (float)quantity;
                                CurrentStockHistSales[itemID].SellMth3 += totalSaleVal;
                                CurrentStockHistSales[itemID].COGSMth3 += cost;
                            }
                            else if (date.Month == 4)
                            {
                                CurrentStockHistSales[itemID].SoldMth4 += (float)quantity;
                                CurrentStockHistSales[itemID].SellMth4 += totalSaleVal;
                                CurrentStockHistSales[itemID].COGSMth4 += cost;
                            }
                            else if (date.Month == 5)
                            {
                                CurrentStockHistSales[itemID].SoldMth5 += (float)quantity;
                                CurrentStockHistSales[itemID].SellMth5 += totalSaleVal;
                                CurrentStockHistSales[itemID].COGSMth5 += cost;
                            }
                            else if (date.Month == 6)
                            {
                                CurrentStockHistSales[itemID].SoldMth6 += (float)quantity;
                                CurrentStockHistSales[itemID].SellMth6 += totalSaleVal;
                                CurrentStockHistSales[itemID].COGSMth6 += cost;
                            }
                            else if (date.Month == 7)
                            {
                                CurrentStockHistSales[itemID].SoldMth7 += (float)quantity;
                                CurrentStockHistSales[itemID].SellMth7 += totalSaleVal;
                                CurrentStockHistSales[itemID].COGSMth7 += cost;
                            }
                            else if (date.Month == 8)
                            {
                                CurrentStockHistSales[itemID].SoldMth8 += (float)quantity;
                                CurrentStockHistSales[itemID].SellMth8 += totalSaleVal;
                                CurrentStockHistSales[itemID].COGSMth8 += cost;
                            }
                            else if (date.Month == 9)
                            {
                                CurrentStockHistSales[itemID].SoldMth9 += (float)quantity;
                                CurrentStockHistSales[itemID].SellMth9 += totalSaleVal;
                                CurrentStockHistSales[itemID].COGSMth9 += cost;
                            }
                            else if (date.Month == 10)
                            {
                                CurrentStockHistSales[itemID].SoldMth10 += (float)quantity;
                                CurrentStockHistSales[itemID].SellMth10 += totalSaleVal;
                                CurrentStockHistSales[itemID].COGSMth10 += cost;
                            }
                            else if (date.Month == 11)
                            {
                                CurrentStockHistSales[itemID].SoldMth11 += (float)quantity;
                                CurrentStockHistSales[itemID].SellMth11 += totalSaleVal;
                                CurrentStockHistSales[itemID].COGSMth11 += cost;
                            }
                            else if (date.Month == 12)
                            {
                                CurrentStockHistSales[itemID].SoldMth12 += (float)quantity;
                                CurrentStockHistSales[itemID].SellMth12 += totalSaleVal;
                                CurrentStockHistSales[itemID].COGSMth12 += cost;
                            }
                        }
                        else if (date >= startLastHistMonth)
                        {
                            if (!LYStockHistSales.ContainsKey(itemID))
                                LYStockHistSales[itemID] = new HistSales();

                            if (date.Month == 1)
                            {
                                LYStockHistSales[itemID].SoldMth1 += (float)quantity;
                                LYStockHistSales[itemID].SellMth1 += totalSaleVal;
                                LYStockHistSales[itemID].COGSMth1 += cost;
                            }
                            else if (date.Month == 2)
                            {
                                LYStockHistSales[itemID].SoldMth2 += (float)quantity;
                                LYStockHistSales[itemID].SellMth2 += totalSaleVal;
                                LYStockHistSales[itemID].COGSMth2 += cost;
                            }
                            else if (date.Month == 3)
                            {
                                LYStockHistSales[itemID].SoldMth3 += (float)quantity;
                                LYStockHistSales[itemID].SellMth3 += totalSaleVal;
                                LYStockHistSales[itemID].COGSMth3 += cost;
                            }
                            else if (date.Month == 4)
                            {
                                LYStockHistSales[itemID].SoldMth4 += (float)quantity;
                                LYStockHistSales[itemID].SellMth4 += totalSaleVal;
                                LYStockHistSales[itemID].COGSMth4 += cost;
                            }
                            else if (date.Month == 5)
                            {
                                LYStockHistSales[itemID].SoldMth5 += (float)quantity;
                                LYStockHistSales[itemID].SellMth5 += totalSaleVal;
                                LYStockHistSales[itemID].COGSMth5 += cost;
                            }
                            else if (date.Month == 6)
                            {
                                LYStockHistSales[itemID].SoldMth6 += (float)quantity;
                                LYStockHistSales[itemID].SellMth6 += totalSaleVal;
                                LYStockHistSales[itemID].COGSMth6 += cost;
                            }
                            else if (date.Month == 7)
                            {
                                LYStockHistSales[itemID].SoldMth7 += (float)quantity;
                                LYStockHistSales[itemID].SellMth7 += totalSaleVal;
                                LYStockHistSales[itemID].COGSMth7 += cost;
                            }
                            else if (date.Month == 8)
                            {
                                LYStockHistSales[itemID].SoldMth8 += (float)quantity;
                                LYStockHistSales[itemID].SellMth8 += totalSaleVal;
                                LYStockHistSales[itemID].COGSMth8 += cost;
                            }
                            else if (date.Month == 9)
                            {
                                LYStockHistSales[itemID].SoldMth9 += (float)quantity;
                                LYStockHistSales[itemID].SellMth9 += totalSaleVal;
                                LYStockHistSales[itemID].COGSMth9 += cost;
                            }
                            else if (date.Month == 10)
                            {
                                LYStockHistSales[itemID].SoldMth10 += (float)quantity;
                                LYStockHistSales[itemID].SellMth10 += totalSaleVal;
                                LYStockHistSales[itemID].COGSMth10 += cost;
                            }
                            else if (date.Month == 11)
                            {
                                LYStockHistSales[itemID].SoldMth11 += (float)quantity;
                                LYStockHistSales[itemID].SellMth11 += totalSaleVal;
                                LYStockHistSales[itemID].COGSMth11 += cost;
                            }
                            else if (date.Month == 12)
                            {
                                LYStockHistSales[itemID].SoldMth12 += (float)quantity;
                                LYStockHistSales[itemID].SellMth12 += totalSaleVal;
                                LYStockHistSales[itemID].COGSMth12 += cost;
                            }
                        }
                        else
                        {
                            //save to totals
                            if (!TotalStockSales.ContainsKey(itemID))
                                TotalStockSales[itemID] = new Sale();

                            TotalStockSales[itemID].SoldQty += (float)quantity;
                            TotalStockSales[itemID].SellVal += totalSaleVal;
                            TotalStockSales[itemID].COGS += cost;
                        }
                    }
                }
            }
            finally
            {
                dbConn.Close();
            }
        }

        private static Dictionary<int, HistPurchase> CurrentStockHistPurchases;
        private static Dictionary<int, HistPurchase> LYStockHistPurchases;
        private static Dictionary<int, Purchase> CurrentStockPurchases;
        private static Dictionary<int, Purchase> TotalStockPurchases;
        public static void PopulateOrderItemHistory(String ConnectionString)
        {
            OleDbConnection dbConn = new OleDbConnection(ConnectionString);
            try
            {
                DateTime currentDateTime = DateTime.Now;
                DateTime startCurrentMonth = new DateTime(currentDateTime.Year, currentDateTime.Month, 1);
                DateTime startCurHistMonth = startCurrentMonth.AddYears(-1);
                DateTime startLastHistMonth = startCurHistMonth.AddYears(-1);

                CurrentStockHistPurchases = new Dictionary<int, HistPurchase>();
                LYStockHistPurchases = new Dictionary<int, HistPurchase>();
                CurrentStockPurchases = new Dictionary<int, Purchase>();
                TotalStockPurchases = new Dictionary<int, Purchase>();

                dbConn.Open();
                using (OleDbCommand command = dbConn.CreateCommand())
                {
                    command.CommandText = string.Format(@"SELECT Orders.OrderTime AS MonthYear, OrderInvoiceItems.StockID, SUM(OrderInvoiceItems.QtyInv) AS Qty, SUM(OrderInvoiceItems.RealCost) AS PurValue
                    FROM (Orders INNER JOIN OrderInvoiceItems ON Orders.OrderID = OrderInvoiceItems.OrderID) 
                    WHERE(Orders.Delivered = - 1) AND (OrderInvoiceItems.Delivered = - 1)
                    GROUP BY Orders.OrderTime, OrderInvoiceItems.StockID
                    HAVING (OrderInvoiceItems.StockID > 0)");

                    OleDbDataReader reader = command.ExecuteReader();

                    while (reader.Read())
                    {
                        DateTime date = reader.GetDateTime(0);
                        int itemID = reader.GetInt32(1);
                        float quantity = (float)reader.GetDouble(2);
                        decimal cost = (decimal)reader.GetDouble(3);

                        decimal totalCost = cost * (decimal)quantity / 100;

                        if (date >= startCurrentMonth)
                        {
                            if (!CurrentStockPurchases.ContainsKey(itemID))
                                CurrentStockPurchases[itemID] = new Purchase();

                            CurrentStockPurchases[itemID].PurQty += quantity;
                            CurrentStockPurchases[itemID].Cost += totalCost;
                        }
                        else if (date >= startCurHistMonth)
                        {
                            //save to start
                            if (!CurrentStockHistPurchases.ContainsKey(itemID))
                                CurrentStockHistPurchases[itemID] = new HistPurchase();

                            if (date.Month == 1)
                            {
                                CurrentStockHistPurchases[itemID].PurMth1 += quantity;
                                CurrentStockHistPurchases[itemID].CostMth1 += totalCost;
                            }
                            else if (date.Month == 2)
                            {
                                CurrentStockHistPurchases[itemID].PurMth2 += quantity;
                                CurrentStockHistPurchases[itemID].CostMth2 += totalCost;
                            }
                            else if (date.Month == 3)
                            {
                                CurrentStockHistPurchases[itemID].PurMth3 += quantity;
                                CurrentStockHistPurchases[itemID].CostMth3 += totalCost;
                            }
                            else if (date.Month == 4)
                            {
                                CurrentStockHistPurchases[itemID].PurMth4 += quantity;
                                CurrentStockHistPurchases[itemID].CostMth4 += totalCost;
                            }
                            else if (date.Month == 5)
                            {
                                CurrentStockHistPurchases[itemID].PurMth5 += quantity;
                                CurrentStockHistPurchases[itemID].CostMth5 += totalCost;
                            }
                            else if (date.Month == 6)
                            {
                                CurrentStockHistPurchases[itemID].PurMth6 += quantity;
                                CurrentStockHistPurchases[itemID].CostMth6 += totalCost;
                            }
                            else if (date.Month == 7)
                            {
                                CurrentStockHistPurchases[itemID].PurMth7 += quantity;
                                CurrentStockHistPurchases[itemID].CostMth7 += totalCost;
                            }
                            else if (date.Month == 8)
                            {
                                CurrentStockHistPurchases[itemID].PurMth8 += quantity;
                                CurrentStockHistPurchases[itemID].CostMth8 += totalCost;
                            }
                            else if (date.Month == 9)
                            {
                                CurrentStockHistPurchases[itemID].PurMth9 += quantity;
                                CurrentStockHistPurchases[itemID].CostMth9 += totalCost;
                            }
                            else if (date.Month == 10)
                            {
                                CurrentStockHistPurchases[itemID].PurMth10 += quantity;
                                CurrentStockHistPurchases[itemID].CostMth10 += totalCost;
                            }
                            else if (date.Month == 11)
                            {
                                CurrentStockHistPurchases[itemID].PurMth11 += quantity;
                                CurrentStockHistPurchases[itemID].CostMth11 += totalCost;
                            }
                            else if (date.Month == 12)
                            {
                                CurrentStockHistPurchases[itemID].PurMth12 += quantity;
                                CurrentStockHistPurchases[itemID].CostMth12 += totalCost;
                            }
                        }
                        else if (date >= startLastHistMonth)
                        {
                            if (!LYStockHistPurchases.ContainsKey(itemID))
                                LYStockHistPurchases[itemID] = new HistPurchase();

                            if (date.Month == 1)
                            {
                                LYStockHistPurchases[itemID].PurMth1 += quantity;
                                LYStockHistPurchases[itemID].CostMth1 += totalCost;
                            }
                            else if (date.Month == 2)
                            {
                                LYStockHistPurchases[itemID].PurMth2 += quantity;
                                LYStockHistPurchases[itemID].CostMth2 += totalCost;
                            }
                            else if (date.Month == 3)
                            {
                                LYStockHistPurchases[itemID].PurMth3 += quantity;
                                LYStockHistPurchases[itemID].CostMth3 += totalCost;
                            }
                            else if (date.Month == 4)
                            {
                                LYStockHistPurchases[itemID].PurMth4 += quantity;
                                LYStockHistPurchases[itemID].CostMth4 += totalCost;
                            }
                            else if (date.Month == 5)
                            {
                                LYStockHistPurchases[itemID].PurMth5 += quantity;
                                LYStockHistPurchases[itemID].CostMth5 += totalCost;
                            }
                            else if (date.Month == 6)
                            {
                                LYStockHistPurchases[itemID].PurMth6 += quantity;
                                LYStockHistPurchases[itemID].CostMth6 += totalCost;
                            }
                            else if (date.Month == 7)
                            {
                                LYStockHistPurchases[itemID].PurMth7 += quantity;
                                LYStockHistPurchases[itemID].CostMth7 += totalCost;
                            }
                            else if (date.Month == 8)
                            {
                                LYStockHistPurchases[itemID].PurMth8 += quantity;
                                LYStockHistPurchases[itemID].CostMth8 += totalCost;
                            }
                            else if (date.Month == 9)
                            {
                                LYStockHistPurchases[itemID].PurMth9 += quantity;
                                LYStockHistPurchases[itemID].CostMth9 += totalCost;
                            }
                            else if (date.Month == 10)
                            {
                                LYStockHistPurchases[itemID].PurMth10 += quantity;
                                LYStockHistPurchases[itemID].CostMth10 += totalCost;
                            }
                            else if (date.Month == 11)
                            {
                                LYStockHistPurchases[itemID].PurMth11 += quantity;
                                LYStockHistPurchases[itemID].CostMth11 += totalCost;
                            }
                            else if (date.Month == 12)
                            {
                                LYStockHistPurchases[itemID].PurMth12 += quantity;
                                LYStockHistPurchases[itemID].CostMth12 += totalCost;
                            }
                        }
                        else
                        {
                            //save to totals
                            if (!TotalStockPurchases.ContainsKey(itemID))
                                TotalStockPurchases[itemID] = new Purchase();

                            TotalStockPurchases[itemID].PurQty += quantity;
                            TotalStockPurchases[itemID].Cost += totalCost;
                        }
                    }
                }
            }
            finally
            {
                dbConn.Close();
            }
        }

        

        /*
        private static Dictionary<string,int> PLUsupplierMAp;
        public static void PopulatePLUsupplierMap(string LotsConnString)
        {
            PLUsupplierMAp = new Dictionary<string,int>();

            String query = @"select distinct PLU from Stock";
            OleDbConnection conn = null;
            OleDbDataReader reader = null;
            conn = new OleDbConnection(LotsConnString);
            conn.Open();

            OleDbCommand command = new OleDbCommand(query, conn);
            command.CommandTimeout = 120;
            reader = command.ExecuteReader();
            while (reader.Read())
            {
                string PLU = reader.IsDBNull(0)? null : reader.GetString(0);
                if (PLU != null && PLU != "")
                {
                    //TODO: renv - should preload a dictionary of supplier partcodes and use that (instead of querying db each time for every PLU)
                    int spplierID = GetSupplierIDfromPLU(PLU, LotsConnString);
                    PLUsupplierMAp[PLU] = spplierID;
                }
            }

            if (reader != null)
                reader.Close();

            if (conn != null)
                conn.Close();
        }

        public static int GetSupplierIDfromPLU(string PLU, string LotsConnString)
        {
            int supplierID=0; 
            String query = @"select supplierID from partcode where partcode=@partcode";
            OleDbConnection conn = null;
            OleDbDataReader reader = null;
            try
            {
                conn = new OleDbConnection(LotsConnString);
                conn.Open();

                OleDbCommand command = new OleDbCommand(query, conn);
                command.Parameters.AddWithValue("@partcode", PLU);
                command.CommandTimeout = 120;
                reader = command.ExecuteReader();
                if (reader.Read())
                    supplierID = reader.IsDBNull(0) ? 0 : reader.GetInt32(0);
            }
            finally
            {
                if (reader != null)
                    reader.Close();

                if (conn != null)
                    conn.Close();
            }
            return supplierID;
        }
         */ 
    }
}
